import os
import json
import requests
import struct
import threading
import time
#-----------------------------
# install modbus libs
# pip3 install  pymodbus==2.5.3  --低版本
# pip3 install -i https://mirrors.tuna.tsinghua.edu.cn/pypi/web/simple paho-mqtt
#-----------------------------
from pymodbus.client.sync import ModbusSerialClient as ModbusClient
import paho.mqtt.client as mqtt 
#-----------------------------
# pip install pymysql
# pip install pymssql
# pip install DBUtils==1.3  --- 特别要注意 
#-----------------------------
import pymysql
import pymssql
from pymysql.constants import CLIENT
from DBUtils.PooledDB import PooledDB
import os
class TMqClient :
    def __init__(self):
        self.config={
            "code":"",
            "broker":"",
            "uid":"",
            "pwd":"",
            "port": 1883,
            "willTopic":"",
            "onlineTopic":"",
            "willMsg":{
                "flag":"mqtt-client-willmsg" ,
                "status":"offline",
                "code":""
            },
            "onlineMsg":{
                "flag":"mqtt-client-onlineMsg" ,
                "status":"online",
                "code":""
            },
            "autoSubscribeTopics":[]
        }
        self.client = None
        self.clientId = ''
        self.autoSubTopics = []
        self.onConnectSuccess = None
        self.onConnectError = None
        self.onDisConnected = None 
        self.onMessage = None
    def isConnected(self):
        res = False
        try:
            if self.client != None:
                res = self.client.is_connected()
        except Exception as er:
            print(er)
        return res
    def connect(self, asyncFlag): 
        try:
            connectFlag = self.isConnected()
            if not connectFlag:
                self.clientId = 'mid-'+str(time.time()) 
                client = mqtt.Client(client_id = self.clientId)
                uid = self.config['uid']
                pwd = self.config['pwd']
                client.username_pw_set( uid , password=pwd)
                willTopic = self.config['willTopic']
                if not willTopic=='':
                    willMsg = self.config['willMsg'] if 'willMsg' in self.config else {}
                    willMsg['code'] = self.config['code']
                    _payload = json.dumps( willMsg )
                    client.will_set(topic = willTopic , payload=_payload) 
                client.on_connect = self.on_connect
                client.on_disconnect = self.on_disconnect
                client.on_message = self.on_message
                broker = self.config['broker']
                port = self.config['port']
                keepalive = 60                
                client.connect(broker , port , keepalive)
                self.client = client
                if asyncFlag :
                    client.loop_start()
                else:
                    client.loop_forever()
        except Exception as er:
            print(er) 
    def disconnect(self):
        try:
            if self.isConnected():
                self.client.loop_stop()
        except Exception as er:
            print('mqtt on_connect er' , er)
    def on_connect(self, clt , userdata , flags , rc ):
        try:
            msg = "mqtt connect success."
            if rc!=0 :
                msg = "mqtt connect error ( rc="++str(rc)+" )"
            print(msg)
            if rc == 0 :
                for t in self.config['autoSubscribeTopics']:
                    clt.subscribe(t)
                onlineTopic = self.config['onlineTopic']
                if onlineTopic!='':
                    onlineMsg = json.dumps(self.config['onlineMsg'])
                    clt.publish(topic=onlineTopic, payload=json.dumps(onlineMsg), qos=0, retain=False)
                if self.onConnectSuccess!=None:
                    self.onConnectSuccess(self)
                
            else:
                if self.onConnectError!=None:
                    self.onConnectError(self, rc)
                clt = None
        except Exception as er:
            print('mqtt on_connect er' , er)
    def on_disconnect(self, clt , userdata ,  rc ):
        try:
            print('mqtt disconnect' , rc)
            self.disconnect()
        except Exception as er:
            print('mqtt on_disconnect er' , er)
    def on_message(self, clt , userdata ,  message ):
        try:
            print('mqtt rec message')
            if self.onMessage!=None:
                self.onMessage( self , message)
        except Exception as er:
            print('mqtt on_message er' , er)
    def subscribes(self, topics): 
        try:
            if self.isConnected():
                for t in topics:
                    self.client.subscribe(t)
        except Exception as er:
            print(er) 
    def publishMsg(self, _topic , msgData): 
        try:
            if self.isConnected():
                self.client.publish(topic=_topic, payload= msgData, qos=0, retain=False)
        except Exception as er:
            print(er) 
'''
mqclient = TMqClient()
mqclient.config['code'] = 'mqclientTest'
mqclient.config['broker']="hh.hhdata.cn"
mqclient.config['port'] = 31274
mqclient.config['uid']="admin"
mqclient.config['pwd']="hhuc1115"
mqclient.config['willTopic'] = 'sys/willtopic'
mqclient.config['onlineTopic'] = 'sys/onlinetopic'
mqclient.config['autoSubscribeTopics'] = [
    "sys/#"
]
def onConnectSuccess(mc):
    topic  = "sys/msg"
    data = {
        "code": mqclient.config['code'] ,
        "clientId": mqclient.clientId ,
        "msg":"connectSuccess"
    }
    content = json.dumps(data)
    mc.publishMsg( topic , content)
    thread = threading.Thread(target = ping , daemon=True)
    thread.start()
def onConnectError(mc, rc):
    print('connect fail .', mc , rc)
def onMessage(mc , message):
    print('rec mqtt message.')
    print(message.topic , str(message.payload))
def ping():
    try:
        if mqclient.isConnected():
            msg = '{"code":"'+mqclient.config['code']+'"}'
            topic = "sys/ping"
            mqclient.publishMsg(topic, msg)
            time.sleep(60)
            ping()
    except Exception as er:
        print(er)
mqclient.onConnectSuccess = onConnectSuccess
mqclient.onConnectError = onConnectError
mqclient.onMessage = onMessage
mqclient.connect( False )
'''


'''
Error Code:
1001 : db code is error.
1002 : db type is error.
1003 : pool params error.
1004 : query faild.
'''
class TDB():
    def __init__(self ): 
        self.settings = {}
        self.pools ={}
    def setSettings (self, code , dbType , host , port , db , uid , pwd , sqlPath):
        try:
            item = {}
            if code in  self.settings.keys():
                item = self.settings[code]
            else:
                self.settings[code] = item

            item['code'] = code
            item['dbType'] = dbType
            item['host'] = host
            item['port'] = port
            item['db'] = db
            item['uid'] = uid
            item['pwd'] = pwd
            item['sqlPath'] = sqlPath
        except Exception as er:
            print(er)
    def getPool (self, code):
        pool = None
        status = 0
        try:
            if code in self.pools.keys():
                pool = self.pools[code]
                status = 1
            elif code in self.settings.keys():
                settingItem = self.settings[code]
                if settingItem['dbType'] =='mysql':
                    pool , status = self.getMysqlPool(settingItem)
                elif settingItem['dbType'] =='mssql':
                    pool , status  = self.getMssqlPool(settingItem)
                else:
                    status = 1002
            else:
                status = 1001
                print("pool Code is error." , code)
        except Exception as er:
            print(er)
        return  pool , status
    def getMysqlPool(self, settingItem):
        pool = None
        status = 0
        try:
            pool = PooledDB(
                creator=pymysql,
                mincached=1,
                maxcached=100,
                host=settingItem["host"],
                port=settingItem["port"],
                database=settingItem["db"],
                user=settingItem["uid"],
                password=settingItem["pwd"],
                autocommit=True,
                client_flag=CLIENT.MULTI_STATEMENTS
            )
            self.pools[settingItem['code']] = pool
            status = 1
        except Exception as er:
            status = 1003
            print(er)
        return  pool , status
    def getMssqlPool(self, settingItem):
        pool = None
        status = 0
        try:
            pool = PooledDB(
                creator=pymssql,
                maxconnections=200,
                mincached=10,
                maxcached=15,
                maxshared=0,
                maxusage=1000,
                ping =1,
                blocking=True,
                host=settingItem["host"],
                port=settingItem["port"],
                database=settingItem["db"],
                user=settingItem["uid"],
                password=settingItem["pwd"],
                autocommit = True,
                #closeable=False,
            )
            code = settingItem['code']
            self.pools[code] = pool
            status = 1
        except Exception as er:
            status = 1003
            print(er)
        return  pool , status
    def getConnection (self, code):
        conn = None
        status = 0
        try:
            pool , poolStatus= self.getPool(code)
            if poolStatus==1 :
                conn = pool.connection()
                status = 1
            else:
                status = poolStatus
        except Exception as er:
            print(er)
        return  conn , status
    def query (self, code  , sql , params):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            conn , connstatus = self.getConnection(code)
            if connstatus == 1 :
                settingItem = self.settings[code]
                dbType = settingItem['dbType']
                cursor = None
                if dbType == 'mysql':
                    cursor = conn.cursor()
                else:
                    cursor = conn.cursor(as_dict=True)
                try:
                    if params != None:
                        cursor.execute(sql, params)
                    else:
                        cursor.execute(sql)
                    if cursor.description != None:
                        rsList = []
                        rs = cursor.fetchall()
                        rsList.append(list(rs))
                        #queryRes['datasetList'].append(list(rs))
                        while cursor.nextset():
                            rs = cursor.fetchall()
                            rsList.append(list(rs))
                            #queryRes['datasetList'].append(list(rs))
                        queryRes['datasetList'] = rsList
                        if len(queryRes['datasetList']) >0 :
                            queryRes['recordset'] = queryRes['datasetList'][0]
                    else:
                        queryRes['executeResult'] ={
                            "rowNum": cursor.rownumber ,
                            "rowCount": cursor.rowcount ,
                            "newId": cursor.lastrowid
                        }
                    queryRes['queryStatus'] = 1
                except Exception as eer:
                    print('query faild.' , eer)
                    queryRes['queryStatus'] = 1004
                    queryRes['errMsg'] = eer;
                cursor.close()
                cursor = None
            else:
                queryRes['queryStatus'] = connstatus
        except Exception as er:
            print(er)
        return  queryRes
    def queryByKey (self, code  , key , params):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            if code in self.settings.keys():
                fn = os.path.join(self.settings[code]["sqlPath"], key)
                _, ext = os.path.splitext(fn)
                if ext =='' :
                    fn = fn +'.txt'
                if os.path.exists(fn):
                    sql = ""
                    with open(fn, "r", encoding='utf-8') as f:
                        sql = f.read()
                    queryRes = self.query(code , sql ,  params)
                else:
                    print('file not exists :' , fn)
                    queryRes['queryStatus'] = 1005
                    queryRes['errMsg'] = "key("+key+") is not exists."
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return  queryRes
    def createRow (self, code  , table , fieldValues):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            if code in self.settings.keys():
                fields = ','.join([x for x  in fieldValues.keys()])
                values = ",".join(["%("+x+")s" for x in fieldValues.keys()])
                sql = "insert into " + table + "(" + fields +") values (" + values+")"
                queryRes = self.query(code , sql , fieldValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return  queryRes
    def updateRow (self, code  , table , fieldValues , keyValues):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            if code in self.settings.keys():
                fields = ','.join([x+'=%('+x+')s' for x in fieldValues.keys()])
                keys = " and ".join([x+'=%('+x+')s' for x in keyValues.keys()])
                sql = "update "+ table + ' set '+ fields +' where '+ keys
                ps ={}
                for k in fieldValues.keys():
                    ps[k] = fieldValues[k]
                for k in keyValues.keys():
                    ps[k] = keyValues[k]
                queryRes = self.query(code , sql , ps)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return  queryRes
    def deleteRow (self, code  , table  , keyValues):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            if code in self.settings.keys():
                keys = " and ".join([x+'=%('+x+')s' for x in keyValues.keys()])
                sql = "delete from  "+ table + ' where '+ keys
                queryRes = self.query(code , sql , keyValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return  queryRes
    def viewRows (self, code  , view  , keyValues):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            if code in self.settings.keys():
                keys = " and ".join([x+'=%('+x+')s' for x in keyValues.keys()])
                sql = "select * from  "+ view + ' where '+ keys
                queryRes = self.query(code , sql , keyValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return  queryRes
    
        try:
            code = data.params['dbCode']
            view = data.params['view']
            keyValues = data.params['keys']
            data.result = self.viewRows(code , view  , keyValues)
        except Exception as er:
            print(er)
class TTerminal:
    def __init__(self):
        self.terInfo={}
        self.channels={}
        self.points ={}
    def load(self , config  ):
        try:
            if config != None:
                self.terInfo = config['terminalInfo'][0]
                for ch in config['channels']:
                    channel = TTerChannel()
                    channel.terminal = self
                    channel.channelInfo = ch 
                    self.channels[channel.channelInfo['channelCode']] = channel
                for un in config['units']:
                    channel = self.channels[un['channelCode']]
                    unit = TTerUnit()
                    unit.channel = channel
                    unit.unitInfo = un
                    channel.units[un['unitCode']] = unit
                for t in config['tasks']:
                    channelCode = t['channelCode']
                    unitCode = t['unitCode']
                    regFrom = str(t['regFrom'])
                    unit = self.channels[channelCode].units[unitCode]
                    task = TTask()
                    task.taskInfo = t
                    unit.tasks[regFrom] = task
                for p in config['points']:
                    channelCode = p['channelCode']
                    unitCode = p['unitCode']
                    unit = self.channels[channelCode].units[unitCode]
                    self.points[p["pointCode"]]  = p 
                    unit.points[p["pointCode"]]  = p 
                    startRegAddr = p['startRegAddr']
                    regNum = p['regNum']
                    for i in range(startRegAddr , startRegAddr + regNum):
                        unit.registers[i] = 0

            print( self.channels)
        except Exception as err:
            print(err)
    def savePointDatas(self , dataFileName):
        pass
    def start(self):
        pass

class TTerChannel:
    def __init__(self):
        self.terminal = TTerminal()
        self.channelInfo = {}
        self.units ={}
    def connect(self):
        res = False
        try: 
            comPort = self.channelInfo['comPort']
            comBaudrate = self.channelInfo['comBaudrate']
            comDataBit = self.channelInfo['comDataBit']
            comStopBit = self.channelInfo['comStopBit']
            comParity = self.channelInfo['comParity']
            self.client = ModbusClient( method="rtu" , port= comPort , baudrate=comBaudrate , stopbits=comStopBit, parity= comParity, bytesize=comDataBit, timeout=2 )
            self.client.connect()
            res = True
        except Exception as er:
            znComm.log.error(er)
        return  res
    def start(self):
        try:
            for unit in self.units.values():
                unit.startTasksAsync()
        except Exception  as er:
            print(er)
    def savePointDatas(self):
        pass

class TTerUnit:
    def __init__(self):
        self.channel = TTerChannel()
        self.running = False
        self.unitInfo = {}
        self.tasks = {}
        self.registers = {}
        self.points = {}
        self.taskThread = None
    def startTasks(self):
        try:
            self.running = True
            while self.running :
                for task in self.tasks.values():
                    task.apply()
                self.decodePoints()
                self.savePointDatas()
        except Exception  as er:
            print(er)
    def startTasksAsync(self):
        try:
            if self.taskThread == None :
                self.taskThread = threading.Thread(  )
            self.taskThread.start()
        except Exception as er :
            print(er)
    def decodePoints(self):
        pass
    def savePointDatas(self):
        pass

class TTask: 
    def __init__(self):
        self.unit = TTerUnit()
        self.taskInfo = {}
    def apply(self):
        try:
            pass
        except Exception  as er:
            print(er)

#terminal = TTerminal()
#terminal.load(terConfig.config)


    
